﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;

namespace Twi.DataAccess.DBUtility
{
    public class TwiCommandSql : TwiCommand
    {
        public TwiCommandSql()
        {
            base.Initialize();
        }

        public TwiCommandSql(DbCommand cmd)
        {
            base.Initialize(cmd);
        }

        public TwiCommandSql(DbConnection conn, DbTransaction txn)
        {
            base.Initialize(conn, txn);
        }

        /// <summary>
        /// 获取 DbParameterCollection。
        /// 返回结果:Transact-SQL 语句或存储过程的参数。 默认值为空集合。
        /// </summary>
        public override DbParameterCollection Parameters
        {
            get
            {
                return (_cmd as SqlCommand).Parameters;
            }
        }

        /// <summary>
        /// 初始化 DbParameter 类的新实例。
        /// </summary>
        /// <returns>返回 SqlParameter或OracleParameter 实例</returns>
        protected override DbParameter CreateDbParameter()
        {
            return new SqlParameter();
        }

        /// <summary>
        /// 对连接对象执行 SQL 语句。
        /// </summary>
        /// <returns>受影响的行数。</returns>
        public override int ExecuteNonQuery()
        {
            return _cmd.ExecuteNonQuery();
        }


        /// <summary>
        /// 对连接对象执行 SQL 语句。
        /// </summary>
        /// <returns>受影响的行数。</returns>
        public override int ExecuteNonQueryByLog()
        {
            return _cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。 所有其他的列和行将被忽略。
        /// </summary>
        /// <returns>结果集中第一行的第一列。</returns>
        public override object ExecuteScalar()
        {
            return _cmd.ExecuteScalar();
        }

        /// <summary>
        /// 执行查询，返回DbDataReader
        /// </summary>
        /// <returns></returns>
        public override DbDataReader ExecuteReader()
        {
            return _cmd.ExecuteReader();
        }

        #region 新增记录，返回新增记录的ID
        /// <summary>
        /// 新增记录，返回新增记录的ID
        /// </summary>
        /// <param name="_cmd"></param>
        /// <param name="xe"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public override object ExecuteInsert(XElement xe, string tableName = null)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                tableName = xe.Name.LocalName;
            }
            //上传的节点名称跟数据库列名称相符合的节点集合            
            List<XElement> setColumnList = new List<XElement>();
            var metaRows = DBHelper.GetMetaDataTable(tableName).Rows.OfType<DataRow>();
            foreach (XElement x in xe.Elements()
                .Where(y => !new string[] { "CreateUserID", "CreateDate", "LastUpdateUserID", "LastUpdateDate" }.Contains(y.Name.LocalName)))
            {
                DataRow dr = metaRows.Where(y => y["字段名"].ToString() == x.Name.LocalName //匹配列名
                   && y["计算列"].ToString() == "0"  //非计算列
                   && y["标识"].ToString() == "0" //非标识列
                   ).FirstOrDefault();
                if (dr != null)
                {
                    //添加参数
                    (_cmd as SqlCommand).SetParameter(x.ToSqlParameter(dr["类型"].ToString()));
                    setColumnList.Add(x);
                }
            }

            string insertIntoClause_Columns = setColumnList.Select(x => x.Name.LocalName).Aggregate(
                (x, y) => string.Format("{0}, {1}", x, y));
            string selectClause = setColumnList.Select(x => string.Format("{0} = @{0}", x.Name.LocalName)).Aggregate((x, y) => string.Format("{0}, {1}", x, y));


            if (metaRows.FirstOrDefault(x => x["字段名"].ToString() == "CreateUserID") != null) //表中是否包含添加人
            {
                insertIntoClause_Columns += ", CreateUserID";
                selectClause += ", CreateUserID = @CurrentUserID";
            }
            if (metaRows.FirstOrDefault(x => x["字段名"].ToString() == "CreateUserName") != null) //表中是否包含添加人
            {
                insertIntoClause_Columns += ", CreateUserName";
                selectClause += ", CreateUserName = @CurrentUserName";
            }
            if (metaRows.FirstOrDefault(x => x["字段名"].ToString() == "LastUpdateDate") != null)
            {
                insertIntoClause_Columns += ", LastUpdateDate";
                selectClause += ", LastUpdateDate = GETDATE()";
            }

            _cmd.CommandText = string.Format("INSERT INTO {0} ({1})  SELECT {2}", tableName, insertIntoClause_Columns, selectClause);

            //是否存在标识字段
            if (metaRows.FirstOrDefault(x => x["标识"].ToString() == "1") == null)
            {
                return _cmd.ExecuteNonQuery();
            }
            else
            {
                //SqlParameter paraId = new SqlParameter("Id", SqlDbType.Int);
                //paraId.Direction = ParameterDirection.Output;
                //me.SetParameter(paraId);
                //me.CommandText += " SELECT @Id = SCOPE_IDENTITY()" ;
                //me.ExecuteNonQuery();
                //return paraId.Value;

                _cmd.ExecuteNonQuery();

                //IDENT_CURRENT is not limited by scope it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope.
                //SCOPE_IDENTITY, @@IDENTITY 都不行，如果是在事物中，除了第一个ID能获取外，其他的都不能获取 

                _cmd.CommandText = string.Format("SELECT IDENT_CURRENT('{0}')", tableName);
                return _cmd.ExecuteScalar();

            }
        }
        #endregion

        #region 更新记录
        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="xe"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public override int ExecuteUpdate(XElement xe, string tableName = null)
        {
            if (_cmd is SqlCommand)
            {
                SqlExtension.InitUpdateLog(xe, tableName);
            }
            if (string.IsNullOrEmpty(tableName))
            {
                tableName = xe.Name.LocalName;
            }
            //上传的节点名称跟数据库列名称相符合的节点集合            
            List<XElement> setColumnList = new List<XElement>();
            var metaRows = DBHelper.GetMetaDataTable(tableName).Rows.OfType<DataRow>();
            var primaryKeys = DBHelper.GetMetaDataTablePrimaryKeys(tableName);//主键集合 

            foreach (XElement x in xe.Elements().Where(y => !new string[] { "CreateUserID", "CreateDate", "LastUpdateUserID", "LastUpdateDate" }.Contains(y.Name.LocalName)))
            {
                DataRow dr = metaRows.Where(y => y["字段名"].ToString() == x.Name.LocalName //匹配列名
                    && y["计算列"].ToString() == "0"  //非计算列                    
                    ).FirstOrDefault();
                if (dr != null)
                {
                    //添加参数
                    (_cmd as SqlCommand).SetParameter(x.ToSqlParameter(dr["类型"].ToString()));

                    if (!primaryKeys.Contains(x.Name.LocalName) //排除主检(包括组合键)
                        && ((dr["标识"].ToString() == "1" && dr["主键"].ToString() == "1") || (dr["标识"].ToString() == "0" && dr["主键"].ToString() == "0"))//标识主键列或非标识非主键列
                        )
                    {
                        setColumnList.Add(x);
                    }
                }
            }

            string setClause = setColumnList.Select(x => string.Format("{0} = @{0}", x.Name.LocalName)).Aggregate((x, y) => string.Format("{0}, {1}", x, y));
            if (metaRows.FirstOrDefault(x => x["字段名"].ToString().Contains("LastUpdateUserID")) != null) //表中是否包含最后更新人字段
            {
                setClause += ", LastUpdateUserID = @CurrentUserID, LastUpdateDate = GETDATE()";
            }
            if (metaRows.FirstOrDefault(x => x["字段名"].ToString().Contains("LastUpdateUserName")) != null) //不是所有表都有
            {
                setClause += ", LastUpdateUserName = @CurrentUserName";
            }
            _cmd.CommandText = string.Format("UPDATE {0} SET {1} WHERE {2}", tableName, setClause
                , primaryKeys.Select(x => string.Format("{0} = @{0}", x)).Aggregate((x, y) => string.Format("{0} AND {1}", x, y)));
            return _cmd.ExecuteNonQuery();

        }
        #endregion

        #region 获得查询列表
        /// <summary>
        /// 获得查询列表
        /// </summary>
        /// <param name="tableName">节点名称</param>
        /// <returns></returns>
        public override XElement GetMultiple(string tableName = "Item")
        {
            XElement xeMultiple = new XElement(string.Format("{0}List", tableName));
            using (SqlDataReader rdr = (_cmd as SqlCommand).ExecuteReader())
            {
                while (rdr.Read())
                {
                    XElement xeItem = new XElement(tableName);
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        string dataTypeName = rdr.GetDataTypeName(i);
                        XElement xeCell = rdr.ToXElement(_cmd, i, dataTypeName == "xml");
                        if (xeCell.Name.LocalName.StartsWith("override_")) //是否是从其他表中关联过来的覆盖值, 通常用于主表用*号概括所有字段, 从表显示部分需要覆盖的字段 
                        {
                            xeItem.Element(xeCell.Name.LocalName.Replace("override_", "")).SetValue(xeCell.Value);
                        }
                        else
                        {
                            xeItem.Add(xeCell);
                        }
                    }
                    xeMultiple.Add(xeItem);
                }
            }
            return xeMultiple;
        }
        /// <summary>
        /// 获取数据(分页)
        /// </summary>
        /// <param name="masterTableName"></param>
        /// <param name="sql"></param>
        /// <param name="searchCondition"></param>
        /// <param name="otherText"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public override XElement[] GetMultiple(string masterTableName, string sql, string searchCondition, string otherText, int pageSize, int pageIndex)
        {
            string source = string.Format(@"WITH VIEW_SOURCE AS ({0} {1} {2} )", sql, searchCondition, otherText);

            //处理分页信息
            _cmd.CommandText = source + Environment.NewLine + string.Format("SELECT COUNT(*) FROM VIEW_SOURCE");
            object objItemAmount = null;
            try
            {
                objItemAmount = _cmd.ExecuteScalar();
            }
            catch
            {
                throw new Exception("查找总条目数异常: " + Environment.NewLine + _cmd.CommandText);
            }
            int itemAmount = int.Parse(objItemAmount.ToString());
            int pageAmount = itemAmount / pageSize + (itemAmount % pageSize == 0 ? 0 : 1);
            XElement xeItemAmount = new XElement("ItemAmount", itemAmount);
            XElement xePageAmount = new XElement("PageAmount", pageAmount);

            XElement xeMultiple = new XElement(string.Format("{0}List", masterTableName ?? "Item"));

            if (itemAmount == 0)
            {
                return new XElement[] { xeItemAmount, xePageAmount, xeMultiple };
            }

            string source_numbered = source + Environment.NewLine + string.Format(@", VIEW_SOURCE_ROWNUMBERED AS (SELECT VIEW_SOURCE.*,Rownum rn FROM VIEW_SOURCE)");

            //处理列表信息
            _cmd.CommandText = source_numbered + Environment.NewLine + string.Format(@"SELECT * FROM VIEW_SOURCE_ROWNUMBERED WHERE rn between {0} AND {1} "
                , pageSize * pageIndex
                , pageSize * (pageIndex + 1));

            using (var rdr = ExecuteReader())
            {
                while (rdr.Read())
                {
                    XElement xeItem = new XElement(masterTableName ?? "Item");
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        xeItem.Add(rdr.ToXElement(_cmd, i));
                    }
                    xeMultiple.Add(xeItem);
                }
            }
            return new XElement[] { xeItemAmount, xePageAmount, xeMultiple };
        }
        #endregion

        #region 获得数据(分页/或不分页)
        /// <summary>
        /// 获得数据(分页/或不分页)
        /// </summary>
        /// <param name="masterTableName"></param>
        /// <param name="tablesSource"></param>
        /// <param name="searchCondition"></param>
        /// <param name="selectColumns"></param>
        /// <param name="orderExpression"></param>
        /// <param name="xePage"></param>
        /// <param name="subSearchCondition"></param>
        /// <param name="subOrderExpression"></param>
        /// <returns></returns>
        public override XElement[] GetMultiple(string masterTableName, string tablesSource, string searchCondition, string selectColumns, string orderExpression
            , XElement xePage, string subSearchCondition = "", string subOrderExpression = "")
        {
            if (xePage.Element("PageIndex") == null) //不需要分页
            {
                _cmd.CommandText = "SELECT " + selectColumns + " FROM " + tablesSource + " " + searchCondition + " ORDER BY " + orderExpression;
                return new XElement[] { GetMultiple(masterTableName) };
            }
            else if (xePage.Element("PageIndex").Value == "-1") //分页转不分页，导出全部记录时使用
            {
                _cmd.CommandText = string.Format(@"WITH VIEW_SOURCE AS (SELECT {0} FROM {1} WHERE 1 = 1 {2} {3})"
               , subOrderExpression == "" ? selectColumns : ("TOP 100 PERCENT " + selectColumns), tablesSource, subSearchCondition
               , subOrderExpression == "" ? "" : (" ORDER BY " + subOrderExpression));
                //处理自定义的排序子句
                string orderby = "";
                if (!string.IsNullOrEmpty(orderExpression))
                {
                    //同时兼容 a.ApplicationFormNo DESC, SampleNo ASC
                    orderby = orderExpression.Split(',').Select(x => x.Contains('.') ? x.Split('.')[1] : x).Aggregate((x, y) => string.Format("{0}, {1}", x, y));
                }

                string subOrderby = "";
                if (!string.IsNullOrEmpty(subOrderExpression))
                {
                    subOrderby = subOrderExpression.Split(',').Select(x => x.Contains('.') ? x.Split('.')[1] : x).Aggregate((x, y) => string.Format("{0}, {1}", x, y));
                }

                _cmd.CommandText = _cmd.CommandText + Environment.NewLine + "SELECT VIEW_SOURCE.* FROM VIEW_SOURCE " + searchCondition;
                string sqlOrderby = string.IsNullOrEmpty(orderby) ? (string.IsNullOrEmpty(subOrderExpression) ? "" : subOrderby) : orderby;
                if (!string.IsNullOrEmpty(sqlOrderby))
                {
                    _cmd.CommandText += " ORDER BY " + sqlOrderby;
                }
                return new XElement[] { GetMultiple(masterTableName) };
            }
            else
            {
                return GetMultiple(masterTableName, tablesSource, searchCondition, selectColumns, orderExpression, int.Parse(xePage.Element("PageSize").Value), int.Parse(xePage.Element("PageIndex").Value), subSearchCondition, subOrderExpression);
            }
        }

        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="me"></param>
        /// <param name="masterTableName">主表名称</param>
        /// <param name="tablesSource">数据的来源from后（主表 inner join 从表 on 。。）</param>
        /// <param name="searchCondition">搜索条件 where 后（..）/param>
        /// <param name="selectColumns">select后</param>
        /// <param name="orderExpression"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="subSearchCondition">跟在SQl内部的Where条件</param>
        /// <returns></returns>
        private XElement[] GetMultiple(string masterTableName
            , string tablesSource, string searchCondition, string selectColumns, string orderExpression, int pageSize, int pageIndex, string subSearchCondition, string subOrderExpression)
        {
            string source = string.Format(@"WITH VIEW_SOURCE AS (SELECT {0} FROM {1} WHERE 1 = 1 {2} {3})"
                , subOrderExpression == "" ? selectColumns : ("TOP 100 PERCENT " + selectColumns), tablesSource, subSearchCondition
                , subOrderExpression == "" ? "" : (" ORDER BY " + subOrderExpression));

            //处理分页信息
            _cmd.CommandText = source + Environment.NewLine + string.Format("SELECT COUNT(1) FROM VIEW_SOURCE {0} ", searchCondition);
            int itemAmount = (int)_cmd.ExecuteScalar();
            int pageAmount = itemAmount / pageSize + (itemAmount % pageSize == 0 ? 0 : 1);
            XElement xeItemAmount = new XElement("ItemAmount", itemAmount);
            XElement xePageAmount = new XElement("PageAmount", pageAmount);

            XElement xeMultiple = new XElement(string.Format("{0}List", masterTableName ?? "Item"));

            if (itemAmount == 0)
            {
                return new XElement[] { xeItemAmount, xePageAmount, xeMultiple };
            }

            //处理自定义的排序子句
            string orderby = "";
            if (!string.IsNullOrEmpty(orderExpression))
            {
                //同时兼容 a.ApplicationFormNo DESC, SampleNo ASC
                orderby = orderExpression.Split(',').Select(x => x.Contains('.') ? x.Split('.')[1] : x).Aggregate((x, y) => string.Format("{0}, {1}", x, y));
            }

            string subOrderby = "";
            if (!string.IsNullOrEmpty(subOrderExpression))
            {
                subOrderby = subOrderExpression.Split(',').Select(x => x.Contains('.') ? x.Split('.')[1] : x).Aggregate((x, y) => string.Format("{0}, {1}", x, y));
            }

            string source_numbered = source + Environment.NewLine +
                string.Format(@", VIEW_SOURCE_ROWNUMBERED AS (SELECT TOP {0} ROW_NUMBER() OVER (ORDER BY {1}) AS [RowNumber], VIEW_SOURCE.* FROM VIEW_SOURCE {2})"
                , pageSize * (pageIndex + 1)
                , string.IsNullOrEmpty(orderby) ?
                (string.IsNullOrEmpty(subOrderExpression) ? "(SELECT NULL)" : subOrderby)
                : orderby
                , searchCondition);

            //处理列表信息
            _cmd.CommandText = source_numbered + Environment.NewLine + string.Format(@"SELECT * FROM VIEW_SOURCE_ROWNUMBERED WHERE [RowNumber] > {0} ORDER BY [RowNumber]"
                , pageSize * pageIndex);


            using (var rdr = _cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    XElement xeItem = new XElement(masterTableName ?? "Item");
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        xeItem.Add(rdr.ToXElement(_cmd, i));
                    }
                    xeMultiple.Add(xeItem);
                }
            }
            return new XElement[] { xeItemAmount, xePageAmount, xeMultiple };
        }
        #endregion

        #region 获得单条数据
        /// <summary>
        /// 获得单条数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns>查询到记录返回，没有找到记录抛出异常</returns>
        public override XElement GetSingle(string tableName = "Item")
        {
            XElement xe = new XElement(tableName);
            using (var rdr = ExecuteReader())
            {
                if (rdr.Read())
                {
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        string dataTypeName = rdr.GetDataTypeName(i);
                        xe.Add(rdr.ToXElement(_cmd, i, dataTypeName == "xml"));
                    }
                }
                else
                {
                    throw new Exception("无法检索到唯一记录");
                }
            }
            return xe;
        }

        /// <summary>
        /// 获得单条数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public override XElement GetSingleOrEmpty(string tableName = "Item")
        {
            XElement xe = new XElement(tableName);
            using (var rdr = ExecuteReader())
            {
                if (rdr.Read())
                {
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        string dataTypeName = rdr.GetDataTypeName(i);
                        xe.Add(rdr.ToXElement(_cmd, i, dataTypeName == "xml"));
                    }
                }
                else
                {
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        xe.Add(new XElement(rdr.GetName(i)));
                    }
                }
            }
            return xe;
        }

        /// <summary>
        /// 获得单条数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns>查询到记录返回，没有找到记录返回null</returns>
        public override XElement GetSingleOrDefault(string tableName = "Item")
        {
            XElement xe = new XElement(tableName);
            using (var rdr = ExecuteReader())
            {
                if (rdr.Read())
                {
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        string dataTypeName = rdr.GetDataTypeName(i);
                        xe.Add(rdr.ToXElement(_cmd, i, dataTypeName == "xml"));
                    }
                }
                else
                {
                    return null;
                }
            }
            return xe;
        }
        #endregion

        #region 获得数据库当前时间
        /// <summary>
        /// 获得数据库当前时间
        /// </summary>
        /// <returns></returns>
        public override DateTime GetCurrentDate()
        {
            _cmd.CommandText = "SELECT GETDATE()";
            return (DateTime)_cmd.ExecuteScalar();
        }
        #endregion

        #region 添加参数
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="name">参数名</param>
        /// <param name="value">参数值</param>
        public override void AddParameter(string name, object value)
        {
            name = name.ToUpper();
            (_cmd as SqlCommand).Parameters.AddWithValue("@" + name.ToUpper(), value);
        }
        #endregion

        #region 设置参数值(参数存在设置值，不存在添加参数)
        /// <summary>
        /// 设置参数值(参数存在设置值，不存在添加参数)
        /// </summary>
        /// <param name="name">参数名</param>
        /// <param name="value">参数值</param>
        public override void SetParameter(string paraName, object paraValue)
        {
            paraName = paraName.ToUpper();
            if (paraValue is XElement)
            {
                paraValue = new SqlXml(new XmlTextReader(paraValue.ToString(), XmlNodeType.Document, null));
            }
            else if (paraValue is IEnumerable<XElement>)
            {
                paraValue = (paraValue as IEnumerable<XElement>).Select(x => x.ToString()).Aggregate((x, y) => x + y);
            }

            if (_cmd.Parameters.Contains(paraName))
            {
                _cmd.Parameters[paraName].Value = paraValue;
            }
            else
            {
                SqlParameter parameter = new SqlParameter(paraName, paraValue);
                if (paraValue is XElement)
                {
                    parameter.SqlDbType = SqlDbType.Xml;
                }
                _cmd.Parameters.Add(parameter);
            }
        }
        /// <summary>
        /// 设置参数值(参数存在设置值，不存在添加参数)
        /// </summary>
        /// <param name="parameter">参数</param>
        public override void SetParameter(DbParameter parameter)
        {
            string paraName = parameter.ParameterName.ToUpper();
            object paraValue = parameter.Value;
            if (_cmd.Parameters.Contains(paraName))
            {
                _cmd.Parameters[paraName].Value = paraValue;
            }
            else
            {
                SqlParameter newPara = new SqlParameter(paraName, paraValue);
                if (paraValue is XElement)
                {
                    newPara.SqlDbType = SqlDbType.Xml;
                }
                _cmd.Parameters.Add(newPara);
            }
        }
        #endregion

        #region 设置参数并返回当前参数
        /// <summary>
        /// 设置参数并返回当前参数
        /// </summary>
        /// <param name="paraName"></param>
        /// <param name="type"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public override DbParameter SetParameter_InputOutput(string paraName, GSDbType type, int size = 0)
        {
            if (!paraName.StartsWith("@"))
            {
                paraName = "@" + paraName;
            }
            if (_cmd.Parameters.Contains(paraName))
            {
                return _cmd.Parameters[paraName];
            }
            else
            {
                SqlParameter para = new SqlParameter();
                para.ParameterName = paraName;
                para.Direction = ParameterDirection.InputOutput;
                para.SqlValue = DBNull.Value;
                para.SqlDbType = (SqlDbType)type;
                if (size != 0)
                {
                    para.Size = size;
                }
                _cmd.Parameters.Add(para);
                return para;
            }
        }
        #endregion

        #region 连接字符串
        /// <summary>
        /// 连接字符串
        /// </summary>
        /// <param name="separartor"></param>
        /// <param name="format"></param>
        /// <returns></returns>
        public override string GetConcatenate(string separartor, string format = "{0}")
        {
            string concat = "";
            using (var rdr = _cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    if (concat != "")
                    {
                        concat += separartor;
                    }
                    concat += string.Format(format, rdr.GetValue(0));
                }
            }
            return concat;
        }
        #endregion

    }
}

